package cn.com.libertymutual.sp.dao;

import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import cn.com.libertymutual.sp.bean.TbSpScoreLogIn;

@Repository
public interface ScoreLogInDao extends PagingAndSortingRepository<TbSpScoreLogIn, Integer>, JpaSpecificationExecutor<TbSpScoreLogIn> {

	TbSpScoreLogIn findByUserCodeAndStatus(String userCode, String status);

	@Query(" from TbSpScoreLogIn t where t.reasonNo=?1 and t.userCode = ?2 and t.status =1")
	List<TbSpScoreLogIn> findInScoreHis(String policyNo, String userCode);

	/**
	 * 查询过期积分
	 * @return
	 */
	@Query(value = "select * from tb_sp_scorelog_in t where balance>0 and invalid_date<=sysdate() and status='1'", nativeQuery = true)
	List<TbSpScoreLogIn> findInvalidate();

	/**
	 * 更新过期积分
	 * @return
	 */
	// @Transactional
	// @Modifying
	// @Query("update TbSpScoreLogIn t set status='0' where balance<acChangeScore
	// and invalidDate<=sysdate() and status='1'")
	// List<TbSpScoreLogIn> updateInvalidate();

	/**
	 * 查询将生效积分
	 * @return
	 */
	@Query(value = "select * from tb_sp_scorelog_in where effictive_date<=sysdate() and ac_changescore<re_changescore and status='1'", nativeQuery = true)
	List<TbSpScoreLogIn> findNoeffictive();

	/**
	 * 更新生效积分
	 * @return
	 */
	// @Transactional
	// @Modifying
	// @Query("update TbSpScoreLogIn set
	// acChangeScore=reChangeScore,balance=reChangeScore where acChangeScore=0 and
	// effictiveDate<=sysdate() and status='1' ")
	// public int updateEffictive();

	// @Query("select t from TbSpScoreLogIn t where t.userCode = ?1 and
	// t.effictiveDate >?3 and t.status =1")
	// List<TbSpScoreLogIn> findByUCodeAndDate(String userCode, String date);

	@Query(value = "select SUM(balance),invalid_date from tb_sp_scorelog_in where user_code = ?1 and invalid_date =(select invalid_date from tb_sp_scorelog_in where user_code=?1 and balance>0 and effictive_date<=SYSDATE() ORDER BY invalid_date limit 1) group by invalid_date", nativeQuery = true)
	List<String> findNearExpireByUCode(String userCode);

	@Query(value = "select SUM((re_changescore-ac_changescore)),invalid_date from tb_sp_scorelog_in where user_code = ?1 and invalid_date =(select invalid_date from tb_sp_scorelog_in where user_code=?1 and balance=0 and re_changescore>0 and effictive_date>SYSDATE() ORDER BY invalid_date limit 1) group by invalid_date", nativeQuery = true)
	List<String> findNearExpireWByUCode(String userCode);

	@Query(value = "SELECT date_format(change_time,'%Y-%m-%d') as date, Sum(re_changescore) AS score FROM tb_sp_scorelog_in where user_code = ?1 and change_time >= ?2 and status =1 GROUP BY date_format(change_time,'%Y-%m-%d' )", nativeQuery = true)
	List<String> findByUCodeAndDate(String userCode, Date date);

	@Query("select COALESCE(sum(t.reChangeScore),0) from TbSpScoreLogIn t where t.userCode = ?1 and t.effictiveDate >=?2 and t.status =1")
	int findSumScoreByUCodeAndDate(String userCode, Date date);

	@Query(value = "select * from (select t1.id,1 as type, t1.change_type,t1.re_changescore,t1.ac_changescore,t1.reason,t1.reason_no,t1.change_time,t1.remark from tb_sp_scorelog_in as t1 where t1.user_code = :userCode and t1.status=1) q union  "
			+ "select * from (select t2.id,2 as type,t2.change_type,t2.re_changescore,t2.ac_changescore,t2.reason,t2.reason_no,t2.change_time,t2.remark from tb_sp_scorelog_out as t2 where t2.user_code = :userCode and t2.status=1) p order by change_time desc limit :pageNumber , :pageSize", nativeQuery = true)
	List<String> findAllInAndOut(@Param("userCode") String userCode, @Param("pageNumber") int pageNumber, @Param("pageSize") int pageSize);

	@Query(value = "select t1.id,1 as type,t1.change_type,t1.re_changescore,t1.ac_changescore,t1.reason,t1.reason_no,t1.change_time,t1.remark from tb_sp_scorelog_in as t1 where t1.user_code = :userCode and t1.status=1 order by t1.change_time desc limit :pageNumber , :pageSize", nativeQuery = true)
	List<String> findAllIn(@Param("userCode") String userCode, @Param("pageNumber") int pageNumber, @Param("pageSize") int pageSize);

	// 所有已生效积分
	@Query("select t from TbSpScoreLogIn t where t.userCode = ?1 and t.status =1 and t.balance>0 order by t.invalidDate asc")
	List<TbSpScoreLogIn> findAllEffect(String userCode);

	// 所有待生效积分
	@Query("select t from TbSpScoreLogIn t where t.userCode = ?1 and t.status =1 and t.balance=0 and t.effictiveDate > date_format(SYSDATE(),'%Y-%m-%d')  order by t.invalidDate asc")
	List<TbSpScoreLogIn> findAllWaitEffect(String userCode);

	@Query(value = "select * from tb_sp_scorelog_in where user_code = ?1 and status =1 order by id desc LIMIT 1", nativeQuery = true)
	TbSpScoreLogIn findLastOneByUCode(String userCode);

	@Query(value = "select * from tb_sp_scorelog_in where user_code = ?1 and status =1 and balance>0 and invalid_date >=sysdate() order by invalid_date asc LIMIT 1", nativeQuery = true)
	TbSpScoreLogIn findLastEffectByUCode(String userCode);

	@Query(value = "select * from tb_sp_scorelog_in where change_type = ?1 and status =1 and policy_no=?2", nativeQuery = true)
	List<TbSpScoreLogIn> findByPolicyNo(String changeType, String policyNo);

	@Query(value = "select * from tb_sp_scorelog_in where status =1 and policy_no=?1", nativeQuery = true)
	List<TbSpScoreLogIn> findByPolicyNo(String policyNo);

	// 修改用户编码
	@Transactional
	@Modifying(clearAutomatically = true)
	@Query("update TbSpScoreLogIn set userCode = :userCode, telephone = :telephone where userCode = :userCode1 and telephone = :telephone1")
	public int updateUserCodeByUserCode(@Param("userCode") String userCode, @Param("telephone") String telephone,
			@Param("userCode1") String userCode1, @Param("telephone1") String telephone1);

	@Query(value = "select * from tb_sp_scorelog_in where user_code = ?1 and status =1 and policy_no=?2", nativeQuery = true)
	List<TbSpScoreLogIn> findByUCodeAndPolicyNo(String userCode, String policyNo);
	
	@Query(value = "select * from tb_sp_scorelog_in where user_code = ?1 and status =1 and policy_no=?2 and change_type in (?3)", nativeQuery = true)
	TbSpScoreLogIn findByUCodeAndPolicyNoAndType(String userCode, String policyNo,List<String> changeTypes);
	
	@Query(value = "select i.*,u.USER_NAME from tb_sp_scorelog_in i,tb_sp_user u  where u.USER_CODE = i.user_code and i.user_code = ?1 and i.status =1 and i.policy_no=?2 and i.change_type in (?3)", nativeQuery = true)
	Object findByUpUserScore(String userCode, String policyNo,List<String> changeTypes);
}
